/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.lang.*;
import TO.BookTO;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author admin-pc
 */
public class BookDAOImpl implements BookDAO {

    Connection con = null;
    PreparedStatement ptmt = null;
    ResultSet rs = null;

    private static Logger log = Logger.getLogger(BookDAOImpl.class.getName());

    private final String INSERT = "insert into book values(seq.nextval,?,?)";
    private final String DELETE = "delete from book where bookid=?";
    private final String UPDATE_STATUS = "update book set status=? where bookid=?";
    private final String UPDATE_TITTLE = "update book set title=? where bookid=?";
    private final String FIND_ALL = "SELECT * FROM book";
    private final String FIND_BY_PK = "select * from book where bookid=?";
    private final String FIND_BY_AUTH = "select * from book where bookid in "
            + "(select bookid from collection where authorid in"
            + "(select authorid from author where name like=?))";

    BookDAOImpl() {

    }

    private Connection getConnection() throws SQLException {
        Connection conn;
        conn = ConnectionFactory.getInstance().getConnection();
        return conn;
    }

    public void add(BookTO bookTo) {
        try {
            con = getConnection();
            ptmt = con.prepareStatement(INSERT);
            ptmt.setString(1, bookTo.getBookTitle());
            ptmt.setString(2, bookTo.getBookStatus());
            ptmt.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }

    }

    public void delete(int bookId) {
        try {
            con = getConnection();
            ptmt = con.prepareStatement(DELETE);
            ptmt.setInt(1, bookId);
            ptmt.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }

    }

    public void updateStatus(BookTO bookTo) {
        try {
            con = getConnection();
            ptmt = con.prepareStatement(UPDATE_STATUS);
            ptmt.setString(1, bookTo.getBookStatus());
            ptmt.setInt(2, bookTo.getBookId());
            ptmt.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }

    }

    public void updateTittle(BookTO bookTo) {
        try {
            con = getConnection();
            ptmt = con.prepareStatement(UPDATE_TITTLE);
            ptmt.setString(1, bookTo.getBookStatus());
            ptmt.setInt(2, bookTo.getBookId());
            ptmt.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }

    }

    public List findAll() {
        List books = new ArrayList<BookTO>();
        BookTO bookTo = null;
        try {
            con = getConnection();
            ptmt = con.prepareStatement(FIND_ALL);
            rs = ptmt.executeQuery();
            while (rs.next()) {
                bookTo = new BookTO();
                bookTo.setBookId(rs.getInt(1));
                bookTo.setBookTitle(rs.getString(2));
                bookTo.setBookStatus(rs.getString(3));
                books.add(bookTo);
            }

        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }
        return books;
    }

    public BookTO findByPK(int bookId) {
        BookTO bookTo = null;
        try {
            con = getConnection();
            ptmt = con.prepareStatement(FIND_BY_PK);
            ptmt.setInt(1, bookId);
            rs = ptmt.executeQuery();
            if (rs.next()) {
                bookTo = new BookTO();
                bookTo.setBookId(rs.getInt(1));
                bookTo.setBookTitle(rs.getString(2));
                bookTo.setBookStatus(rs.getString(3));
            }
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }
        return bookTo;
    }

    public BookTO findByAuth(String authName) {
        BookTO bookTo = null;
        try {
            con = getConnection();
            ptmt = con.prepareStatement(FIND_BY_AUTH);
            ptmt.setString(1, authName);
            rs = ptmt.executeQuery();
            if (rs.next()) {
                bookTo = new BookTO();
                bookTo.setBookId(rs.getInt(1));
                bookTo.setBookTitle(rs.getString(2));
                bookTo.setBookStatus(rs.getString(3));
            }
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Exception: ", e);
        }
        finally {
            if (ptmt != null) {
                try {
                    ptmt.close();
                }
                catch (SQLException e) {
                    log.log(Level.SEVERE, "Exception: ", e);
                }
            }
        }
        return bookTo;
    }

}
